from pyparsing import Literal, CaselessLiteral, Word, Upcase, delimitedList, Optional, \
    Combine, Group, alphas, nums, alphanums, ParseException, Forward, oneOf, quotedString, \
    ZeroOrMore, restOfLine
def test( str ):
    print str,"->"
    try:
        tokens = SQL.parseString( str )
        print "tokens = ",        tokens
        print "tokens.columns =", tokens.columns
        print "tokens.tables =",  tokens.tables
        print "tokens.where =", tokens.where
        print "tokens.fields =", tokens.fields
        print "tokens.type =",  tokens.type
	print "tokens.path =",  tokens.path
	print "tokens.id =",  tokens.id
        print "tokens.var =", tokens.var
    except ParseException, err:
        print " "*err.loc + "^\n" + err.msg
        print err
    print


# define SQL tokens
sqlStmt = Forward()
selectToken = CaselessLiteral( "select" )
fromToken   = CaselessLiteral( "from" )
insertToken = CaselessLiteral( "insert" )
intoToken   = CaselessLiteral( "into" )
valueToken = CaselessLiteral( "values" )
createToken = CaselessLiteral( "create" )

ident          = Word( alphas, alphanums + "_$" ).setName("identifier")
columnName     = Upcase( delimitedList( ident, ".", combine=True ) )
columnNameList = Group( delimitedList( columnName ) )
tableName      = Upcase( delimitedList( ident, ".", combine=True ) )
tableNameList  = Group( delimitedList( tableName ) )
valueName = Upcase( delimitedList( ident, ".", combine=True ) )
valueNameList = Group( delimitedList( valueName | quotedString))

# keywords

varchar_    = Literal("varchar")
int_        = Literal("int")
smallint_   = Literal("smallint")
tinyint_    = Literal("tinyint")
decimal_    = Literal("decimal")
numeric_    = Literal("numeric")
char_       = Literal("char")
date_       = Literal("date")
varTypeName = oneOf("char varchar smallint numeric tinyint decimal date int") + Optional("(" + Word(alphanums) + ")")

typeName      = Upcase( delimitedList( ident, ".", combine=True ) )
typeNameList  = Group( delimitedList( typeName ) )
idName      = Upcase( delimitedList( ident, ".", combine=True ) )
idNameList  = Group( delimitedList( idName ) )
varTypeNameList = ZeroOrMore(Group(ident + varTypeName))
pathident =  Word(alphas + alphanums + "/" + "'")
pathName =  Upcase( delimitedList( pathident, "/", combine=True ) )
pathNameList = Group( delimitedList( pathName ) )

whereExpression = Forward()
and_ = CaselessLiteral("and")
or_ = CaselessLiteral("or")
in_ = CaselessLiteral("in")

E = CaselessLiteral("E")
binop = oneOf("= != < > >= <= eq ne lt le gt ge like", caseless=True)
arithSign = Word("+-",exact=1)
realNum = Combine( Optional(arithSign) + ( Word( nums ) + "." + Optional( Word(nums) )  |
                                                         ( "." + Word(nums) ) ) + 
            Optional( E + Optional(arithSign) + Word(nums) ) )
intNum = Combine( Optional(arithSign) + Word( nums ) + 
            Optional( E + Optional("+") + Word(nums) ) )

columnRval = realNum | intNum | quotedString | columnName # need to add support for alg expressions
whereCondition = Group(
    ( columnName + binop + columnRval ) |
    ( columnName + in_ + "(" + delimitedList( columnRval ) + ")" ) |
    ( columnName + in_ + "(" + sqlStmt + ")" ) |
    ( "(" + whereExpression + ")" )
    )
whereExpression << whereCondition + ZeroOrMore( ( and_ | or_ ) + whereExpression ) 

# define the grammar
sqlStmt      << ( (selectToken + 
                   ( '*' | columnNameList ).setResultsName( "columns" ) + 
                   fromToken + 
                   tableNameList.setResultsName( "tables" ) + 
                   Optional( Group( CaselessLiteral("where") + whereExpression ), "" ).setResultsName("where")) | 
                   ( insertToken + intoToken + tableNameList.setResultsName("tables") +
                   Optional( "(" + columnNameList.setResultsName("columns") + ")") +
                   valueToken + "(" + valueNameList.setResultsName("fields") + ")" +
                   Optional( Group( CaselessLiteral("where") + whereExpression ), "" ).setResultsName("where")) |
                   ( createToken + typeNameList.setResultsName("type") +
                     Optional(pathNameList.setResultsName("path")) +
                     idNameList.setResultsName("id") +
                     varTypeNameList.setResultsName("var")) )

SQL = sqlStmt
SQL.validate()

# define Oracle comment format, and ignore them
oracleSqlComment = "--" + restOfLine
SQL.ignore( oracleSqlComment )


test( "Select A, B, C from Sys.dual" )
test( "Insert into sdfsdf(a,b,c) values(a,b,c)" )
test( "CREATE DATABASE '/desired/path/to/db'  MYDB test int test1 int(30)" )